package com.hsnn.medstgmini.settlement.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.model.Whether;
import com.hsnn.medstgmini.settlement.dao.BankAcctInfoDao;
import com.hsnn.medstgmini.settlement.model.SetAcctInfo;
import com.hsnn.medstgmini.settlement.model.SetBankAcctInfo;
import com.hsnn.medstgmini.settlement.model.SetBankAcctInfoLog;
import com.hsnn.medstgmini.settlement.model.SetBankArea;
import com.hsnn.medstgmini.settlement.model.SetBankInfo;
import com.hsnn.medstgmini.settlement.model.form.BankAcct;
import com.hsnn.medstgmini.settlement.model.form.BankAcctLog;
import com.hsnn.medstgmini.sys.model.AuditType;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.sys.model.UserType;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;

/**
 * @category 帐户、银行卡管理
 * @author 蔡春龙
 * @date 2015年7月21日
 */
@Repository
public class BankAcctInfoDaoImpl implements BankAcctInfoDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;
	
	@Autowired
	private HibernateSqlUtil dao;
	/**
	 * @category 获取帐户信息
	 * @date 2015年7月21日
	 * @param user当前登陆人
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public SetAcctInfo getAcctInfo(SysUser user) {
		Session session = factory.getCurrentSession();
		Criteria criteria = session.createCriteria(SetAcctInfo.class);
		String orgId = "";
		if (user.getMain() == Whether.yes.getKey()) {
			orgId = user.getOrgId();
		} else { // 非主帐号获取主帐号帐户信息
			orgId = user.getRootUser().getOrgId();
		}
		criteria.add(Restrictions.eq("orgId", orgId));
		List<SetAcctInfo> list = criteria.list();
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * @category 根据id查询帐户信息
	 * @date 2015年7月23日
	 * @param orgId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public SetAcctInfo getAcctInfoById(String orgId) {
		Session session = factory.getCurrentSession();
		if (StringUtils.isNotBlank(orgId)) {
			Criteria criteria = session.createCriteria(SetAcctInfo.class);
			criteria.add(Restrictions.eq("orgId", orgId));
			List<SetAcctInfo> list = criteria.list();
			if (list != null && !list.isEmpty()) {
				return list.get(0);
			}
		}
		return null;
	}

	/**
	 * @category 获取开户行
	 * @date 2015年7月21日
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getBankList() {
		Session session = factory.getCurrentSession();
		String sql = "SELECT DISTINCT bank_acct_no bankAcctNo, father_bank_name fatherBankName FROM set_bankinfo WHERE bank_acct_no IS NOT NULL ORDER BY bank_acct_no ASC ";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 查询银行卡列表
	 * @date 2015年7月21日
	 * @param page翻页参数
	 * @param bankAcct查询参数
	 * @param user登陆人
	 * @return
	 */
	public Pagination getBankAcctInfoList(Pagination page,
			SetBankAcctInfo bankAcct, SysUser user) {
		String orgId = "";
		if (user.getMain() == Whether.yes.getKey()) {
			orgId = user.getOrgId();
		} else { // 非主帐号获取主帐号帐户信息
			orgId = user.getRootUser().getOrgId();
		}
		bankAcct = bankAcct == null ? new SetBankAcctInfo() : bankAcct;
		bankAcct.setSettleAcctId(orgId);
		return getBankAcctInfoList(page, bankAcct);
	}

	/**
	 * @category 根据帐户id查询银行卡列表
	 * @date 2015年7月21日
	 * @param page翻页参数
	 * @param bankAcct查询参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getBankAcctInfoList(Pagination page,
			SetBankAcctInfo bankAcct) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.bank_acct_id bankAcctId, a.settle_acct_id settleAcctId, a.bank_acct_no bankAcctNo, a.acct_holder acctHolder, a.deposit_bank depositBank, a.deposit_bank_no depositBankNo, a.deposit_bank_name depositBankName, a.deposit_bank_area_id depositBankAreaId, a.deposit_bank_area_name depositBankAreaName, a.acct_approval_pic_dir acctApprovalPicDir, a.acct_approval_shortcut_dir acctApprovalShortcutDir, a.submit_time submitTime, a.submit_user submitUser, a.audit_status auditStatus, a.audit_time auditTime, a.audit_user auditUser, a.audit_remark auditRemark, a.is_default_acct isDefaultAcct, a.upd_user updUser, a.upd_datetime updDatetime, b.name submitUserName, c.name auditUserName, d.father_bank_name fatherBankName FROM set_bankacctinfo a LEFT JOIN sys_user b on a.submit_user = b.id LEFT JOIN sys_user c on a.audit_user = c.id LEFT JOIN ( SELECT DISTINCT bank_acct_no, father_bank_name FROM set_bankinfo ) d ON a.deposit_bank = d.bank_acct_no WHERE a.settle_acct_id = :settleAcctId  ");

		Integer auditStatus = bankAcct.getAuditStatus();
		if (auditStatus != null) {
			listSb.append("AND a.audit_status = :auditStatus ");
		}
		String bankAcctNo = bankAcct.getBankAcctNo();
		if (StringUtils.isNotBlank(bankAcctNo)) {
			listSb.append("AND a.bank_acct_no = :bankAcctNo ");
		}
		String acctHolder = bankAcct.getAcctHolder();
		if (StringUtils.isNotBlank(acctHolder)) {
			listSb.append("AND a.acct_holder LIKE :acctHolder ");
		}
		listSb.append(" ORDER BY CASE a.audit_status WHEN 0 THEN 3 WHEN 2 THEN 2  WHEN 1 THEN 1 END");

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("settleAcctId", bankAcct.getSettleAcctId());
		listQuery.setString("settleAcctId", bankAcct.getSettleAcctId());

		if (auditStatus != null) {
			countQuery.setInteger("auditStatus", auditStatus);
			listQuery.setInteger("auditStatus", auditStatus);
		}
		if (StringUtils.isNotBlank(bankAcctNo)) {
			countQuery.setString("bankAcctNo", bankAcctNo);
			listQuery.setString("bankAcctNo", bankAcctNo);
		}
		if (StringUtils.isNotBlank(acctHolder)) {
			countQuery.setString("acctHolder", "%" + acctHolder + "%");
			listQuery.setString("acctHolder", "%" + acctHolder + "%");
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				auditStatus = Integer.valueOf((Byte) map.get("auditStatus"));
				if (auditStatus != null) {
					String auditStatusName = AuditType.getName(auditStatus);
					map.put("auditStatusName", auditStatusName);
				}
				Date submitTime = (Date) map.get("submitTime");
				if (submitTime != null) {
					map.put("submitTime", DateUtil.dateTime(submitTime));
				}
				Date auditTime = (Date) map.get("auditTime");
				if (auditTime != null) {
					map.put("auditTime", DateUtil.dateTime(auditTime));
				}
			}
		}

		return page;
	}

	/**
	 * @category 查询银行支行列表
	 * @date 2015年7月21日
	 * @param page翻页参数
	 * @param bankInfo查询参数
	 * @param user登陆人
	 * @return
	 */
	public Pagination getSubBankAcctInfoList(Pagination page,
			SetBankInfo bankInfo) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.bank_code bankCode, a.bank_name bankName, a.bank_acct_no bankAcctNo, a.father_bank_name fatherBankName, a.ec_flag ecFlag, a.eis_bank_code eisBankCode, a.eis_site_code eisSiteCode, b.area_code areaCode, b.area_name areaName FROM set_bankinfo a JOIN set_bankarea b on a.bank_area_id = b.area_code WHERE a.bank_acct_no = :bankAcctNo ");

		String bankCode = bankInfo.getBankCode();
		if (StringUtils.isNotBlank(bankCode)) {
			listSb.append("AND a.bank_code = :bankCode ");
		}
		String bankName = bankInfo.getBankName();
		if (StringUtils.isNotBlank(bankName)) {
			listSb.append("AND a.bank_name like :bankName ");
		}
		String bankAreaId = bankInfo.getBankAreaId();
		if (StringUtils.isNotBlank(bankAreaId)) {
			listSb.append("AND b.area_code = :bankAreaId ");
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("bankAcctNo", bankInfo.getBankAcctNo());
		listQuery.setString("bankAcctNo", bankInfo.getBankAcctNo());

		if (StringUtils.isNotBlank(bankCode)) {
			countQuery.setString("bankCode", bankCode);
			listQuery.setString("bankCode", bankCode);
		}
		if (StringUtils.isNotBlank(bankName)) {
			countQuery.setString("bankName", "%" + bankName + "%");
			listQuery.setString("bankName", "%" + bankName + "%");
		}
		if (StringUtils.isNotBlank(bankAreaId)) {
			countQuery.setString("bankAreaId", bankAreaId);
			listQuery.setString("bankAreaId", bankAreaId);
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		page.setRows(listQuery.list());

		return page;
	}

	/**
	 * @category 查询银行地区列表
	 * @date 2015年7月22日
	 * @param bankArea
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SetBankArea> getBankAreaList(SetBankArea bankArea) {
		bankArea = bankArea == null ? new SetBankArea() : bankArea;
		String provinceCode = bankArea.getProvinceCode();
		if (StringUtils.isBlank(provinceCode)) {
			provinceCode = "0";
		}
		String cityCode = bankArea.getCityCode();
		if (StringUtils.isBlank(cityCode)) {
			cityCode = "0";
		}

		Session session = factory.getCurrentSession();
		Criteria criteria = session.createCriteria(SetBankArea.class);

		criteria.add(Restrictions.eq("provinceCode", provinceCode));
		criteria.add(Restrictions.eq("cityCode", cityCode));

		criteria.addOrder(Order.asc("areaCode"));
		return criteria.list();
	}

	/**
	 * @category 保存银行帐户信息
	 * @date 2015年7月22日
	 * @param bankAcct
	 * @param user
	 */
	public void saveBankAcctInfo(SetBankAcctInfo bankAcct, SysUser user) {
		Session session = factory.getCurrentSession();
		if (bankAcct != null) {
			bankAcct.setSubmitTime(new Date());
			bankAcct.setSubmitUser(user.getId());
			bankAcct.setUpdDatetime(new Date());
			bankAcct.setUpdUser(user.getId());
			bankAcct.setAuditUser(null);
			bankAcct.setAuditTime(null);
			bankAcct.setAuditRemark(null);
			bankAcct.setAuditStatus(AuditType.normal.getKey());
			// 获取当前机构编码
			String settleAcctId = "";
			if (user.getMain() == Whether.yes.getKey()) {
				settleAcctId = user.getOrgId();
			} else { // 非主帐号获取主帐号帐户信息
				settleAcctId = user.getRootUser().getOrgId();
			}
			bankAcct.setSettleAcctId(settleAcctId);
			String bankAcctId = bankAcct.getBankAcctId();
			if (StringUtils.isNotBlank(bankAcctId)) {
				SetBankAcctInfo old = getBankAcctById(bankAcctId);
				if (old != null) {
					bankAcct.setIsDefaultAcct(old.getIsDefaultAcct());

					// 编辑时未上传新许可证，不更新许可证
					if (StringUtils.isBlank(bankAcct.getAcctApprovalPicDir())) {
						bankAcct.setAcctApprovalPicDir(old
								.getAcctApprovalPicDir());
						bankAcct.setAcctApprovalShortcutDir(old
								.getAcctApprovalShortcutDir());
					}
					session.evict(old);
				}
				session.update(bankAcct);
			} else {
				bankAcct.setBankAcctId(UUID.randomUUID().toString());
				session.save(bankAcct);
			}

			// 记录日志
			SetBankAcctInfoLog log = new SetBankAcctInfoLog();
			BeanUtils.copyProperties(bankAcct, log);
			log.setBankAcctInfoLogId(UUID.randomUUID().toString());
			session.save(log);

			session.flush();
		}
	}

	/**
	 * @category 保存银行帐户审核信息
	 * @date 2015年7月22日
	 * @param bankAcct
	 * @param user
	 */
	public void saveAuditBankAcctInfo(SetBankAcctInfo bankAcct, SysUser user) {
		Session session = factory.getCurrentSession();
		if (bankAcct != null) {
			
			Integer auditStatus = bankAcct.getAuditStatus();
			if (auditStatus == null) {
				throw new MSException("非法操作！");
			}
			if (auditStatus != 1 && auditStatus != 2) {
				throw new MSException("非法操作！");
			}

			String bankAcctId = bankAcct.getBankAcctId();
			SetBankAcctInfo old = getBankAcctById(bankAcctId);
			if (old != null) {
				//	更新审核状态和备注
				old.setUpdDatetime(new Date());
				old.setUpdUser(user.getId());
				old.setAuditTime(new Date());
				old.setAuditUser(user.getId());
				old.setAuditStatus(auditStatus);
				old.setAuditRemark(bankAcct.getAuditRemark());
				session.update(old);
				
				// 记录日志
				SetBankAcctInfoLog log = new SetBankAcctInfoLog();
				BeanUtils.copyProperties(old, log);
				log.setBankAcctInfoLogId(UUID.randomUUID().toString());
				session.save(log);

				session.flush();
			}
		}
	}

	/**
	 * @category 中心、卫生局编辑银行帐户信息
	 * @date 2015年7月22日
	 * @param bankAcct
	 * @param user
	 */
	public void saveBankAcctInfoByCenter(SetBankAcctInfo bankAcct, SysUser user) {
		Session session = factory.getCurrentSession();
		if (bankAcct != null) {
			bankAcct.setUpdDatetime(new Date());
			bankAcct.setUpdUser(user.getId());

			String bankAcctId = bankAcct.getBankAcctId();
			SetBankAcctInfo old = getBankAcctById(bankAcctId);
			if (old != null) {
				bankAcct.setSettleAcctId(old.getSettleAcctId());
				bankAcct.setSubmitTime(old.getSubmitTime());
				bankAcct.setSubmitUser(old.getSubmitUser());
				bankAcct.setAuditStatus(old.getAuditStatus());
				bankAcct.setAuditUser(old.getAuditUser());
				bankAcct.setAuditTime(old.getAuditTime());
				bankAcct.setAuditRemark(old.getAuditRemark());
				bankAcct.setIsDefaultAcct(old.getIsDefaultAcct());

				// 审核时未上传新许可证，不更新许可证
				if (StringUtils.isBlank(bankAcct.getAcctApprovalPicDir())) {
					bankAcct.setAcctApprovalPicDir(old.getAcctApprovalPicDir());
					bankAcct.setAcctApprovalShortcutDir(old
							.getAcctApprovalShortcutDir());
				}
				session.evict(old);
			}

			session.update(bankAcct);

			// 记录日志
			SetBankAcctInfoLog log = new SetBankAcctInfoLog();
			BeanUtils.copyProperties(bankAcct, log);
			log.setBankAcctInfoLogId(UUID.randomUUID().toString());
			session.save(log);

			session.flush();
		}
	}

	/**
	 * @category 根据银行帐户id查询
	 * @date 2015年7月22日
	 * @param bankAcctId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public SetBankAcctInfo getBankAcctById(String bankAcctId) {
		Session session = factory.getCurrentSession();
		/*Criteria criteria = session.createCriteria(SetBankAcctInfo.class);
		criteria.add(Restrictions.eq("bankAcctId", bankAcctId));*/
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT A.*, B.father_bank_name AS fatherBankName FROM set_bankacctinfo A");
		sql.append(" LEFT JOIN set_bankinfo B ON A.deposit_bank_no = B.bank_code where 1 = 1");
		if(StringUtils.isNotBlank(bankAcctId)){
			sql.append(" and A.bank_acct_id = :bankAcctId"); 
		}
		Query query = session.createSQLQuery(sql.toString()).addEntity(SetBankAcctInfo.class);
		if(StringUtils.isNotBlank(bankAcctId)){
			sql.append(" and A.bank_acct_id = :bankAcctId"); 
		}
		query.setParameter("bankAcctId", bankAcctId);
		List<SetBankAcctInfo> list = query.list();
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * @category 中心根据orgType查询帐户列表
	 * @date 2015年7月22日
	 * @param acctInfo
	 *            (orgType参考UserType枚举)
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getAcctInfoList(SetAcctInfo acctInfo, Pagination page) {
		Session session = factory.getCurrentSession();
		Criteria criteria = session.createCriteria(SetAcctInfo.class);

		criteria.add(Restrictions.eq("orgType", acctInfo.getOrgType()));

		String orgCode = acctInfo.getOrgCode();
		if (StringUtils.isNotBlank(orgCode)) {
			criteria.add(Restrictions.eq("orgCode", orgCode));
		}
		String orgName = acctInfo.getOrgName();
		if (StringUtils.isNotBlank(orgName)) {
			criteria.add(Restrictions.like("orgName", orgName,
					MatchMode.ANYWHERE));
		}

		// 查询个数
		criteria.setProjection(Projections.rowCount());
		Long records = (Long) criteria.uniqueResult();
		page.setRecords(records);

		// 查询列表
		criteria.setProjection(null);
		criteria.addOrder(Order.desc("updDatetime"));
		criteria.setFirstResult(page.getFirstResult());
		criteria.setMaxResults(page.getMaxResults());
		List<SetBankAcctInfo> list = criteria.list();
		page.setRows(list);
		/*Session session = factory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.bank_acct_id bankAcctId, a.settle_acct_id settleAcctId, a.bank_acct_no bankAcctNo, a.acct_holder acctHolder,")
		   .append(" a.deposit_bank depositBank, a.deposit_bank_no depositBankNo, a.deposit_bank_name depositBankName, a.deposit_bank_area_id depositBankAreaId,")
		   .append(" a.deposit_bank_area_name depositBankAreaName, a.acct_approval_pic_dir acctApprovalPicDir, a.acct_approval_shortcut_dir acctApprovalShortcutDir,")
		   .append(" a.submit_time submitTime,a.submit_user submitUser,a.audit_status auditStatus,a.audit_time auditTime,a.audit_user auditUser,")
		   .append(" a.audit_remark auditRemark,a.is_default_acct isDefaultAcct,a.upd_user updUser,a.upd_datetime updDatetime,b. NAME submitUserName,")
		   .append(" c. NAME auditUserName,d.father_bank_name fatherBankName FROM set_bankacctinfo a")
		   .append(" LEFT JOIN sys_user b ON a.submit_user = b.id LEFT JOIN sys_user c ON a.audit_user = c.id")
		   .append(" LEFT JOIN (SELECT DISTINCT bank_acct_no,father_bank_name FROM set_bankinfo) d ON a.deposit_bank = d.bank_acct_no")
		   .append(" WHERE 1= 1 ");
		
		
		sql.append("ORDER BY CASE a.audit_status  WHEN 0 THEN 3 END ");
		Query query = session.createSQLQuery(sql.toString()).addEntity(SetAcctInfo.class);
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		List<SetAcctInfo> list = query.list();
		page.setRows(list);*/
		return page;
	}

	/**
	 * @category 查询银行卡日志
	 * @date 2015年7月23日
	 * @param bankAcctLog
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getBankAcctLogList(BankAcctLog bankAcctLog,
			Pagination page) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.bank_acct_info_log_id bankAcctInfoLogId, a.bank_acct_id bankAcctId, a.settle_acct_id settleAcctId, a.bank_acct_no bankAcctNo, a.acct_holder acctHolder, a.deposit_bank depositBank, a.deposit_bank_no depositBankNo, a.deposit_bank_name depositBankName, a.deposit_bank_area_id depositBankAreaId, a.deposit_bank_area_name depositBankAreaName, a.acct_approval_pic_dir acctApprovalPicDir, a.acct_approval_shortcut_dir acctApprovalShortcutDir, a.submit_time submitTime, a.submit_user submitUser, a.audit_status auditStatus, a.audit_time auditTime, a.audit_user auditUser, a.audit_remark auditRemark, a.is_default_acct isDefaultAcct, a.upd_user updUser, a.upd_datetime updDatetime, b.name submitUserName, c.name auditUserName FROM set_bankacctinfo_log a LEFT JOIN sys_user b on a.submit_user = b.id LEFT JOIN sys_user c on a.audit_user = c.id WHERE a.bank_acct_id = :bankAcctId  ");

		Integer auditStatus = bankAcctLog.getAuditStatus();
		if (auditStatus != null) {
			listSb.append("AND a.audit_status = :auditStatus ");
		}
		Date submitStartTime = bankAcctLog.getSubmitStartTime();
		if (submitStartTime != null) {
			listSb.append("AND DATE_FORMAT(a.submit_time, '%Y-%m-%d') >= :submitStartTime ");
		}
		Date submitEndTime = bankAcctLog.getSubmitEndTime();
		if (submitEndTime != null) {
			listSb.append("AND DATE_FORMAT(a.submit_time, '%Y-%m-%d') <= :submitEndTime ");
		}
		Date auditStartTime = bankAcctLog.getAuditStartTime();
		if (auditStartTime != null) {
			listSb.append("AND DATE_FORMAT(a.audit_time, '%Y-%m-%d') >= :auditStartTime ");
		}
		Date auditEndTime = bankAcctLog.getAuditEndTime();
		if (auditEndTime != null) {
			listSb.append("AND DATE_FORMAT(a.audit_time, '%Y-%m-%d') <= :auditEndTime ");
		}

		listSb.append("ORDER BY a.upd_datetime DESC ");

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("bankAcctId", bankAcctLog.getBankAcctId());
		listQuery.setString("bankAcctId", bankAcctLog.getBankAcctId());

		if (auditStatus != null) {
			countQuery.setInteger("auditStatus", auditStatus);
			listQuery.setInteger("auditStatus", auditStatus);
		}
		if (submitStartTime != null) {
			countQuery.setString("submitStartTime",
					DateUtil.date(submitStartTime));
			listQuery.setString("submitStartTime",
					DateUtil.date(submitStartTime));
		}
		if (submitEndTime != null) {
			countQuery.setString("submitEndTime", DateUtil.date(submitEndTime));
			listQuery.setString("submitEndTime", DateUtil.date(submitEndTime));
		}
		if (auditStartTime != null) {
			countQuery.setString("auditStartTime",
					DateUtil.date(auditStartTime));
			listQuery
					.setString("auditStartTime", DateUtil.date(auditStartTime));
		}
		if (auditEndTime != null) {
			countQuery.setString("auditEndTime", DateUtil.date(auditEndTime));
			listQuery.setString("auditEndTime", DateUtil.date(auditEndTime));
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				if (null != map.get("auditStatus")) {
					Integer auditStatus1 = Integer.valueOf((Byte) map
							.get("auditStatus"));
					String auditStatusName = AuditType.getName(auditStatus1);
					map.put("auditStatusName", auditStatusName);
				}
				Date submitTime = (Date) map.get("submitTime");
				if (submitTime != null) {
					map.put("submitTime", DateUtil.dateTime(submitTime));
				}
				Date auditTime = (Date) map.get("auditTime");
				if (auditTime != null) {
					map.put("auditTime", DateUtil.dateTime(auditTime));
				}
			}
		}

		return page;
	}

	/**
	 * @category 查询核算中心能审核的医疗机构账户列表
	 * @date 2015年7月24日
	 * @param acctInfo
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getAuditHospAcctList(SetAcctInfo acctInfo, SysUser user,
			Pagination page) {
		if (user.getType() != UserType.hszx.getKey()) {
			throw new MSException("当前登陆用户不是卫生局，无权访问！");
		}

		// 获取当前机构编码
		String orgId = "";
		if (user.getMain() == Whether.yes.getKey()) {
			orgId = user.getOrgId();
		} else { // 非主帐号获取主帐号帐户信息
			orgId = user.getRootUser().getOrgId();
		}

		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.org_id orgId, a.org_code orgCode, a.org_name orgName, a.org_type orgType, a.outstand_amt outstandAmt, a.outstand_amt_upd_dttm outstandAmtUpdDttm, a.available_amt availableAmt, a.available_amt_upd_dttm availableAmtUpdDttm, a.pending_amt pendingAmt, a.pending_amt_upd_dttm pendingAmtUpdDttm, a.upd_user updUser, a.upd_datetime updDateTime FROM set_acctinfo a JOIN set_relationship b ON a.org_id = b.org_id WHERE a.org_type = :orgType AND b.father_org_id = :orgId ");

		String orgCode = acctInfo.getOrgCode();
		if (StringUtils.isNotBlank(orgCode)) {
			listSb.append("AND a.org_code = :orgCode ");
		}
		String orgName = acctInfo.getOrgName();
		if (StringUtils.isNotBlank(orgName)) {
			listSb.append("AND a.org_name LIKE :orgName ");
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setInteger("orgType", UserType.yy.getKey());
		listQuery.setInteger("orgType", UserType.yy.getKey());
		countQuery.setString("orgId", orgId);
		listQuery.setString("orgId", orgId);

		if (StringUtils.isNotBlank(orgCode)) {
			countQuery.setString("orgCode", orgCode);
			listQuery.setString("orgCode", orgCode);
		}
		if (StringUtils.isNotBlank(orgName)) {
			countQuery.setString("orgName", "%" + orgName + "%");
			listQuery.setString("orgName", "%" + orgName + "%");
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				BigDecimal outstandAmt = (BigDecimal) map.get("outstandAmt");
				if (outstandAmt != null) {
					map.put("outstandAmt",
							ParseNumber.toStrDecimal(outstandAmt.doubleValue()));
				}
				BigDecimal availableAmt = (BigDecimal) map.get("availableAmt");
				if (availableAmt != null) {
					map.put("availableAmt", ParseNumber
							.toStrDecimal(availableAmt.doubleValue()));
				}
				BigDecimal pendingAmt = (BigDecimal) map.get("pendingAmt");
				if (pendingAmt != null) {
					map.put("pendingAmt",
							ParseNumber.toStrDecimal(pendingAmt.doubleValue()));
				}
			}
		}

		return page;
	}

	/**
	 * @category 根据银行账户的id及当前用户设置默认账户
	 * @author 韩守松
	 * @date 2015年7月27日
	 * @param @param page
	 * @param @param setBankAcctInfo
	 * @param @return
	 */
	public void setDefaultAcctInfoById(Pagination page,
			SetBankAcctInfo setBankAcctInfo, SysUser user) {
		Session session = factory.getCurrentSession();
		if (setBankAcctInfo != null) {
			String bankAcctId = setBankAcctInfo.getBankAcctId();
			SetBankAcctInfo bankAcctInfo = getBankAcctById(bankAcctId);
			if (bankAcctInfo != null) {
				if (user != null) {
					String userId = user.getId();
					if (StringUtils.isNotBlank(bankAcctId)
							&& StringUtils.isNotBlank(userId)) {
						// 同一用户下的银行设为非默认账户
						StringBuffer sb = new StringBuffer();
						sb.append("update set_bankacctinfo set ");
						sb.append("is_default_acct = 0 ");
						sb.append(", upd_user = :updUser ");
						sb.append(", upd_datetime = :updDateTime ");
						sb.append("where 1 = 1 ");
						sb.append("and settle_acct_id = :userId ");
						SQLQuery query = session.createSQLQuery(sb.toString());
						query.setString("updUser", setBankAcctInfo.getUpdUser());
						query.setDate("updDateTime", new Date());
						query.setString("userId", userId);
						query.executeUpdate();

						// 重新设置新默认地址
						bankAcctInfo.setIsDefaultAcct(Whether.yes.getKey());
						bankAcctInfo.setUpdUser(setBankAcctInfo.getUpdUser());
						bankAcctInfo.setUpdDatetime(new Date());
						session.update(bankAcctInfo);
					}
				}
				// 记录日志
				SetBankAcctInfoLog log = new SetBankAcctInfoLog();
				BeanUtils.copyProperties(bankAcctInfo, log);
				log.setBankAcctInfoLogId(UUID.randomUUID().toString());
				session.save(log);
				session.flush();
			}
		}
	}

	/**
	 * @category 核算中心查询医疗机构审核列表
	 * @date 2015年7月30日
	 * @param bankAcct
	 * @param page
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getAuditHospAcctInfoList(BankAcct bankAcct,
			Pagination page, SysUser user) {
		String orgId = "";
		if (user.getMain() == Whether.yes.getKey()) {
			orgId = user.getOrgId();
		} else { // 非主帐号获取主帐号帐户信息
			orgId = user.getRootUser().getOrgId();
		}

		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append(
				"SELECT a.org_id orgId, a.org_code orgCode, a.org_name orgName, b.bank_acct_id bankAcctId,")
				.append(" b.bank_acct_no bankAcctNo, b.acct_holder acctHolder, b.deposit_bank_no depositBankNo, b.deposit_bank_name depositBankName, b.audit_status auditStatus")
				.append(" FROM set_acctinfo a JOIN set_bankacctinfo b ON a.org_id = b.settle_acct_id JOIN set_relationship c ON a.org_id = c.org_id WHERE a.org_type = 4 AND b.audit_status = 0 AND c.father_org_id = :fatherOrgId ");
		if (bankAcct != null) {
			String orgCode = bankAcct.getOrgCode();
			if (StringUtils.isNotBlank(orgCode)) {
				listSb.append("AND a.orgCode = :orgCode ");
			}
			String orgName = bankAcct.getOrgName();
			if (StringUtils.isNotBlank(orgName)) {
				listSb.append("AND a.orgName LIKE :orgName ");
			}
			String bankAcctNo = bankAcct.getBankAcctNo();
			if (StringUtils.isNotBlank(bankAcctNo)) {
				listSb.append("AND b.bank_acct_no = :bankAcctNo ");
			}
			String acctHolder = bankAcct.getAcctHolder();
			if (StringUtils.isNotBlank(acctHolder)) {
				listSb.append("AND b.acct_holder LIKE :acctHolder ");
			}
			Integer auditStatus = bankAcct.getAuditStatus();
			if (auditStatus != null) {
				listSb.append("AND b.audit_status =:auditStatus ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from (" + listSb.toString() + ") ct");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("fatherOrgId", orgId);
		listQuery.setString("fatherOrgId", orgId);

		if (bankAcct != null) {
			String orgCode = bankAcct.getOrgCode();
			if (StringUtils.isNotBlank(orgCode)) {
				countQuery.setString("orgCode", orgCode);
				listQuery.setString("orgCode", orgCode);
			}
			String orgName = bankAcct.getOrgName();
			if (StringUtils.isNotBlank(orgName)) {
				countQuery.setString("orgName", "%" + orgName + "%");
				listQuery.setString("orgName", "%" + orgName + "%");
			}
			String bankAcctNo = bankAcct.getBankAcctNo();
			if (StringUtils.isNotBlank(bankAcctNo)) {
				countQuery.setString("bankAcctNo", bankAcctNo);
				listQuery.setString("bankAcctNo", bankAcctNo);
			}
			String acctHolder = bankAcct.getAcctHolder();
			if (StringUtils.isNotBlank(acctHolder)) {
				countQuery.setString("acctHolder", "%" + acctHolder + "%");
				listQuery.setString("acctHolder", "%" + acctHolder + "%");
			}
			Integer auditStatus = bankAcct.getAuditStatus();
			if (auditStatus != null) {
				countQuery.setInteger("auditStatus", auditStatus);
				listQuery.setInteger("auditStatus", auditStatus);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		return page;
	}

	/**
	 * @category 中心查询医疗机构、配送企业审核列表
	 * @date 2015年7月30日
	 * @param bankAcct
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getAuditAcctInfoList(BankAcct bankAcct, Pagination page) {
		Session session = factory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT da.ar_shortname areaName, b.org_code orgCode, b.org_name orgName ,b.org_id orgId, a.bank_acct_id bankAcctId, a.bank_acct_no bankAcctNo, a.acct_holder acctHolder, a.deposit_bank depositBank, a.deposit_bank_no depositBankNo, a.deposit_bank_name depositBankName, a.deposit_bank_area_id depositBankAreaId, a.deposit_bank_area_name depositBankAreaName, a.acct_approval_pic_dir acctApprovalPicDir, a.acct_approval_shortcut_dir acctApprovalShortcutDir, DATE_FORMAT( a.submit_time, '%Y-%m-%d %T' ) submitTime, c.NAME submitUserName, a.audit_status auditStatus, DATE_FORMAT( a.audit_time, '%Y-%m-%d %T' ) auditTime, d.name auditUserName, a.audit_remark auditRemark FROM set_bankacctinfo a JOIN set_acctinfo b ON a.settle_acct_id = b.org_id LEFT JOIN sys_user c ON a.submit_user = c.id LEFT JOIN sys_user d ON a.audit_user = d.id "
				+ " LEFT JOIN std_hospinfo sh ON b.org_id = sh.hosp_id LEFT JOIN dic_area da on da.area_id=sh.area_id "
				+ " WHERE b.org_type = :orgType ");

		String orgCode = bankAcct.getOrgCode();
		if (StringUtils.isNotBlank(orgCode)) {
			listSb.append("AND b.org_code = :orgCode ");
		}
		String orgName = bankAcct.getOrgName();
		if (StringUtils.isNotBlank(orgName)) {
			listSb.append("AND b.org_name LIKE :orgName ");
		}
		String bankAcctNo = bankAcct.getBankAcctNo();
		if (StringUtils.isNotBlank(bankAcctNo)) {
			listSb.append("AND a.bank_acct_no = :bankAcctNo ");
		}
		String acctHolder = bankAcct.getAcctHolder();
		if (StringUtils.isNotBlank(acctHolder)) {
			listSb.append("AND a.acct_holder LIKE :acctHolder ");
		}
		Integer auditStatus = bankAcct.getAuditStatus();
		if (auditStatus != null) {
			listSb.append("AND a.audit_status = :auditStatus ");
		}
		
		String areaId = page.getConditions().get("areaId");
		if (StringUtils.isNotBlank(areaId)) {
			listSb.append(" AND da.area_id like :areaId ");
		}
		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from (" + listSb.toString() + ") ct");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setInteger("orgType", bankAcct.getOrgType());
		listQuery.setInteger("orgType", bankAcct.getOrgType());

		if (StringUtils.isNotBlank(orgCode)) {
			countQuery.setString("orgCode", orgCode);
			listQuery.setString("orgCode", orgCode);
		}
		if (StringUtils.isNotBlank(orgName)) {
			countQuery.setString("orgName", "%" + orgName + "%");
			listQuery.setString("orgName", "%" + orgName + "%");
		}
		if (StringUtils.isNotBlank(bankAcctNo)) {
			countQuery.setString("bankAcctNo", bankAcctNo);
			listQuery.setString("bankAcctNo", bankAcctNo);
		}
		if (StringUtils.isNotBlank(acctHolder)) {
			countQuery.setString("acctHolder", "%" + acctHolder + "%");
			listQuery.setString("acctHolder", "%" + acctHolder + "%");
		}
		if (auditStatus != null) {
			countQuery.setInteger("auditStatus", auditStatus);
			listQuery.setInteger("auditStatus", auditStatus);
		}
		if (StringUtils.isNotBlank(areaId)) {
			countQuery.setString("areaId","%" + areaId+ "%");
			listQuery.setString("areaId", "%" +areaId+ "%");
		}
		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();
		page.setRows(list);

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				auditStatus = Integer.valueOf((Byte) map.get("auditStatus"));
				if (auditStatus != null) {
					map.put("auditStatusName", AuditType.getName(auditStatus));
				}
			}
		}

		return page;
	}
	
	/**
	 * @category 校验银行卡号是否存在
	 * @date 2015年8月11日
	 * @param bankAcct
	 * @return
	 */
	public boolean checkSameBankAcctNo(SetBankAcctInfo bankAcct) {
		if (bankAcct == null) {
			return false;
		}
		
		String bankAcctId = bankAcct.getBankAcctId();
		String bankAcctNo = bankAcct.getBankAcctNo();
		
		if (StringUtils.isNotBlank(bankAcctNo)) {
			Session session = factory.getCurrentSession();
			Criteria criteria = session.createCriteria(SetBankAcctInfo.class);
			criteria.add(Restrictions.eq("bankAcctNo", bankAcctNo));
			if (StringUtils.isNotBlank(bankAcctId)) {
				criteria.add(Restrictions.not(Restrictions.eq("bankAcctId", bankAcctId)));
			}
			int size = criteria.list().size();
			if (size > 0) {
				return true;
			}
		}
		
		return false;
	}

	/**
	 * @category 中心查询医疗机构
	 * @date 2015年8月12日13:22:20
	 * @author 应晓川
	 * @param Pagination page
	 * @return
	 */
	@Override
	public Pagination getAcctInfoList(Pagination page) {
		
		String areaId  = page.getConditions().get("areaId");
		String orgCode  = page.getConditions().get("orgCode");
		String orgName  = page.getConditions().get("orgName");
		StringBuffer sql = new StringBuffer()
		.append(" select sa.org_id  orgId, ")
		.append(" sa.org_code orgCode, ")
		.append(" sa.org_name orgName, ")
		.append(" da.ar_shortname areaName, ")
		.append(" sa.outstand_amt outstandAmtStr, ")
		.append(" sa.available_amt availableAmtStr, ")
		.append(" sa.pending_amt pendingAmtStr ")
		.append(" from set_acctinfo sa ")
		.append(" left join std_hospinfo sh on sh.hosp_id=sa.org_id ")
		.append(" left join dic_area da on da.area_id = sh.area_id ")
		.append(" where sa.org_type = :orgType   ");
		
		if (StringUtils.isNotBlank(areaId)) {
			sql.append(" and sh.area_id like :areaId ");
			page.getConditions().put("areaId",areaId+"%");
		}
		if (StringUtils.isNotBlank(orgCode)) {
			sql.append(" and sa.org_code = :orgCode ");
		}
		if (StringUtils.isNotBlank(orgName)) {
			sql.append(" and sa.org_name like :orgName ");
			page.getConditions().put("orgName","%"+orgName+"%");
		}	
		
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}



}